{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "152a2525",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import os"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "adbf887d",
   "metadata": {},
   "outputs": [],
   "source": [
    "data_path='../raw_data/music_data'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "883a9ea1",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>item_id</th>\n",
       "      <th>desc</th>\n",
       "      <th>total_timelen</th>\n",
       "      <th>tags</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>93709100</th>\n",
       "      <th>韩国少女时代最新回归新专主打《I GOT A BOY》</th>\n",
       "      <td>韩国少女时代最新回归新专主打《I GOT A BOY》</td>\n",
       "      <td>304</td>\n",
       "      <td>NaN</td>\n",
       "      <td>少女时代,鹿晗,韩国,exo综艺,最新回归,exo快乐大本营,吴世勋,fx组合,音悦台</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>102209100</th>\n",
       "      <th>韩国张力尹携手EXO成员CHEN《呼吸》中文版</th>\n",
       "      <td>韩国张力尹携手EXO成员CHEN《呼吸》中文版</td>\n",
       "      <td>274</td>\n",
       "      <td>NaN</td>\n",
       "      <td>CHEN,少女时代,呼吸,鹿晗,张力尹,韩国,EXO成员,EXO,宋茜,音悦台</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29900100</th>\n",
       "      <th>徐颢菲《猫的借口》</th>\n",
       "      <td>NaN</td>\n",
       "      <td>284</td>\n",
       "      <td>国内</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>368709100</th>\n",
       "      <th>美女翻唱 别问我是谁</th>\n",
       "      <td>美女翻唱 别问我是谁</td>\n",
       "      <td>288</td>\n",
       "      <td>NaN</td>\n",
       "      <td>流行歌曲,翻唱,网络歌曲,美女,舞曲,社会</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>603409100</th>\n",
       "      <th>龙梅子,老猫 老爸老爸你好吗</th>\n",
       "      <td>龙梅子,老猫 老爸老爸你好吗</td>\n",
       "      <td>259</td>\n",
       "      <td>NaN</td>\n",
       "      <td>你好吗,龙梅子,老猫,老爸老爸</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                           item_id  desc  \\\n",
       "93709100  韩国少女时代最新回归新专主打《I GOT A BOY》  韩国少女时代最新回归新专主打《I GOT A BOY》   304   \n",
       "102209100 韩国张力尹携手EXO成员CHEN《呼吸》中文版          韩国张力尹携手EXO成员CHEN《呼吸》中文版   274   \n",
       "29900100  徐颢菲《猫的借口》                                            NaN   284   \n",
       "368709100 美女翻唱 别问我是谁                                    美女翻唱 别问我是谁   288   \n",
       "603409100 龙梅子,老猫 老爸老爸你好吗                            龙梅子,老猫 老爸老爸你好吗   259   \n",
       "\n",
       "                                      total_timelen  \\\n",
       "93709100  韩国少女时代最新回归新专主打《I GOT A BOY》           NaN   \n",
       "102209100 韩国张力尹携手EXO成员CHEN《呼吸》中文版               NaN   \n",
       "29900100  徐颢菲《猫的借口》                              国内   \n",
       "368709100 美女翻唱 别问我是谁                            NaN   \n",
       "603409100 龙梅子,老猫 老爸老爸你好吗                        NaN   \n",
       "\n",
       "                                                                              tags  \n",
       "93709100  韩国少女时代最新回归新专主打《I GOT A BOY》  少女时代,鹿晗,韩国,exo综艺,最新回归,exo快乐大本营,吴世勋,fx组合,音悦台  \n",
       "102209100 韩国张力尹携手EXO成员CHEN《呼吸》中文版          CHEN,少女时代,呼吸,鹿晗,张力尹,韩国,EXO成员,EXO,宋茜,音悦台  \n",
       "29900100  徐颢菲《猫的借口》                                                            NaN  \n",
       "368709100 美女翻唱 别问我是谁                                         流行歌曲,翻唱,网络歌曲,美女,舞曲,社会  \n",
       "603409100 龙梅子,老猫 老爸老爸你好吗                                           你好吗,龙梅子,老猫,老爸老爸  "
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 1、音乐元数据信息\n",
    "music_meta = os.path.join(data_path, 'music_meta')\n",
    "df_music_meta = pd.read_csv(music_meta, sep='\\001', nrows=10, names=['item_id', 'desc', 'total_timelen', 'tags'])\n",
    "df_music_meta.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "80c693ba",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>user_id</th>\n",
       "      <th>gender</th>\n",
       "      <th>age</th>\n",
       "      <th>salary</th>\n",
       "      <th>province</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>00ea9a2fe9c6810aab440c4d8c050000</td>\n",
       "      <td>女</td>\n",
       "      <td>26-35</td>\n",
       "      <td>20000-100000</td>\n",
       "      <td>江苏</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>01a0ae50fd4b9ef6ed04c22a7e421000</td>\n",
       "      <td>女</td>\n",
       "      <td>36-45</td>\n",
       "      <td>0-2000</td>\n",
       "      <td>河北</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>002db7d2360562dd16828c4b91402000</td>\n",
       "      <td>女</td>\n",
       "      <td>46-100</td>\n",
       "      <td>5000-10000</td>\n",
       "      <td>云南</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>006a184749e3b3eb83e9eb516d522000</td>\n",
       "      <td>男</td>\n",
       "      <td>36-45</td>\n",
       "      <td>2000-5000</td>\n",
       "      <td>天津</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>00de61c1d635ad964eef2aefa8292000</td>\n",
       "      <td>女</td>\n",
       "      <td>19-25</td>\n",
       "      <td>2000-5000</td>\n",
       "      <td>内蒙古</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                            user_id gender     age        salary province\n",
       "0  00ea9a2fe9c6810aab440c4d8c050000      女   26-35  20000-100000       江苏\n",
       "1  01a0ae50fd4b9ef6ed04c22a7e421000      女   36-45        0-2000       河北\n",
       "2  002db7d2360562dd16828c4b91402000      女  46-100    5000-10000       云南\n",
       "3  006a184749e3b3eb83e9eb516d522000      男   36-45     2000-5000       天津\n",
       "4  00de61c1d635ad964eef2aefa8292000      女   19-25     2000-5000      内蒙古"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 2、用户元数据信息\n",
    "user_profile = os.path.join(data_path, 'user_profile.data')\n",
    "df_up = pd.read_csv(user_profile, sep=',', nrows=10, names=['user_id', 'gender', 'age', 'salary', 'province'])\n",
    "df_up.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "c04a21dc",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>user_id</th>\n",
       "      <th>item_id</th>\n",
       "      <th>stay_seconds</th>\n",
       "      <th>hour</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>01e069ed67600f1914e64c0fe7730944</td>\n",
       "      <td>4090309101</td>\n",
       "      <td>15</td>\n",
       "      <td>19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>01d86fc1401b283d5828c293be290e08</td>\n",
       "      <td>6192809101</td>\n",
       "      <td>75</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>002f4b9c49be9a0b2c13e1c3c4f6a21c</td>\n",
       "      <td>8915109101</td>\n",
       "      <td>385</td>\n",
       "      <td>18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>01e3fdf415107cd6046a07481fbed499</td>\n",
       "      <td>6470209102</td>\n",
       "      <td>1635</td>\n",
       "      <td>21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>01e3fdf415107cd6046a07481fbed499</td>\n",
       "      <td>6470209102</td>\n",
       "      <td>555</td>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                            user_id     item_id  stay_seconds  hour\n",
       "0  01e069ed67600f1914e64c0fe7730944  4090309101            15    19\n",
       "1  01d86fc1401b283d5828c293be290e08  6192809101            75    12\n",
       "2  002f4b9c49be9a0b2c13e1c3c4f6a21c  8915109101           385    18\n",
       "3  01e3fdf415107cd6046a07481fbed499  6470209102          1635    21\n",
       "4  01e3fdf415107cd6046a07481fbed499  6470209102           555    16"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 3、用户收听音乐相关数据\n",
    "user_watch_pref = os.path.join(data_path, 'user_watch_pref.sml')\n",
    "# print(user_watch_pref)\n",
    "df_uwp = pd.read_csv(user_watch_pref, sep='\\001', names=['user_id', 'item_id', 'stay_seconds', 'hour'])\n",
    "df_uwp.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "1bdc78a1",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>item_id</th>\n",
       "      <th>total_timelen</th>\n",
       "      <th>tags</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>93709100</th>\n",
       "      <th>韩国少女时代最新回归新专主打《I GOT A BOY》</th>\n",
       "      <td>韩国少女时代最新回归新专主打《I GOT A BOY》</td>\n",
       "      <td>-</td>\n",
       "      <td>少女时代,鹿晗,韩国,exo综艺,最新回归,exo快乐大本营,吴世勋,fx组合,音悦台</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>102209100</th>\n",
       "      <th>韩国张力尹携手EXO成员CHEN《呼吸》中文版</th>\n",
       "      <td>韩国张力尹携手EXO成员CHEN《呼吸》中文版</td>\n",
       "      <td>-</td>\n",
       "      <td>CHEN,少女时代,呼吸,鹿晗,张力尹,韩国,EXO成员,EXO,宋茜,音悦台</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29900100</th>\n",
       "      <th>徐颢菲《猫的借口》</th>\n",
       "      <td>-</td>\n",
       "      <td>国内</td>\n",
       "      <td>-</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>368709100</th>\n",
       "      <th>美女翻唱 别问我是谁</th>\n",
       "      <td>美女翻唱 别问我是谁</td>\n",
       "      <td>-</td>\n",
       "      <td>流行歌曲,翻唱,网络歌曲,美女,舞曲,社会</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>603409100</th>\n",
       "      <th>龙梅子,老猫 老爸老爸你好吗</th>\n",
       "      <td>龙梅子,老猫 老爸老爸你好吗</td>\n",
       "      <td>-</td>\n",
       "      <td>你好吗,龙梅子,老猫,老爸老爸</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                           item_id  \\\n",
       "93709100  韩国少女时代最新回归新专主打《I GOT A BOY》  韩国少女时代最新回归新专主打《I GOT A BOY》   \n",
       "102209100 韩国张力尹携手EXO成员CHEN《呼吸》中文版          韩国张力尹携手EXO成员CHEN《呼吸》中文版   \n",
       "29900100  徐颢菲《猫的借口》                                              -   \n",
       "368709100 美女翻唱 别问我是谁                                    美女翻唱 别问我是谁   \n",
       "603409100 龙梅子,老猫 老爸老爸你好吗                            龙梅子,老猫 老爸老爸你好吗   \n",
       "\n",
       "                                      total_timelen  \\\n",
       "93709100  韩国少女时代最新回归新专主打《I GOT A BOY》             -   \n",
       "102209100 韩国张力尹携手EXO成员CHEN《呼吸》中文版                 -   \n",
       "29900100  徐颢菲《猫的借口》                              国内   \n",
       "368709100 美女翻唱 别问我是谁                              -   \n",
       "603409100 龙梅子,老猫 老爸老爸你好吗                          -   \n",
       "\n",
       "                                                                              tags  \n",
       "93709100  韩国少女时代最新回归新专主打《I GOT A BOY》  少女时代,鹿晗,韩国,exo综艺,最新回归,exo快乐大本营,吴世勋,fx组合,音悦台  \n",
       "102209100 韩国张力尹携手EXO成员CHEN《呼吸》中文版          CHEN,少女时代,呼吸,鹿晗,张力尹,韩国,EXO成员,EXO,宋茜,音悦台  \n",
       "29900100  徐颢菲《猫的借口》                                                              -  \n",
       "368709100 美女翻唱 别问我是谁                                         流行歌曲,翻唱,网络歌曲,美女,舞曲,社会  \n",
       "603409100 龙梅子,老猫 老爸老爸你好吗                                           你好吗,龙梅子,老猫,老爸老爸  "
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# df_music_meta 针对NaN 进行ETL\n",
    "df_music_meta = df_music_meta.fillna('-')\n",
    "del df_music_meta['desc']\n",
    "df_music_meta.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e8f52931",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.11.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
